from urllib.parse import quote_plus as urlquote
from sqlalchemy.orm import sessionmaker

DIALECT = 'mysql'
DRIVER = 'pymysql'
USERNAME = 'user_bigData'
PASSWORD = '9X_v4!qN1F%d'
HOST = '192.168.8.23'
PORT = '3306'
DATABASE = 'smart_audit'
# mysql 不会认识utf-8,而需要直接写成utf8
SQLALCHEMY_DATABASE_URI = "{}+{}://{}:{}@{}:{}/{}?charset=utf8".format(DIALECT, DRIVER, USERNAME, urlquote(PASSWORD),
                                                                       HOST, PORT,
                                                                       DATABASE)
from sqlalchemy import create_engine, text
import pandas as pd
engine = create_engine(
    SQLALCHEMY_DATABASE_URI,
    max_overflow=10,  # 超过连接池大小外最多创建的连接
    pool_size=40,  # 连接池大小
    pool_timeout=30,  # 池中没有线程最多等待的时间，否则报错
    pool_recycle=-1,  # 多久之后对线程池中的线程进行一次连接的回收（重置）
    echo=True
)
# Session = sessionmaker(bind=engine)
Session = sessionmaker(autocommit=False, autoflush=False, bind=engine)

session = Session()
sql = 'select constract_name,id ,contract_number  from d_audit_constract_info daci where id >=154'
fetch = session.execute(text(sql))
result = fetch.fetchall()
result1_dict = [row._asdict() for row in result]
print(result1_dict)
items = []
for item in result1_dict:
    sql = 'select name,data from d_audit_constract_data dacd   where info_id =%d' % item['id']
    tt = session.execute(text(sql))
    result = tt.fetchall()
    data = [row._asdict() for row in result]
    t_result = {'合同编号': item['contract_number']}
    for t in data:
        t_result[t['name']] = t['data']
    t_result['合同编号'] =item['contract_number']
    items.append(t_result)
print(items)
df = pd.DataFrame(items)
df.to_csv('ttt4.csv', index=False, sep=',')
session.close()
